<%-- 
    Document   : Relacao
    Created on : 21/06/2013, 19:28:19
    Author     : Daniel
--%>

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@ page import="java.util.*" %>
<%@ page import="javax.sql.*;" %>

<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>Relacao de Ocorrencias por Motorista e Veiculo</title>
    </head>
    <body>
<% 
java.sql.Connection con;
java.sql.Statement s;
java.sql.ResultSet rs;
java.sql.PreparedStatement pst;

con=null;
s=null;
pst=null;
rs=null;

// Remember to change the next line with your own environment 
String url= 
"jdbc:mysql://localhost:3306/sisseg?useUnicode=true";
String id= "root";
String pass = "123456";
try{

Class.forName("org.gjt.mm.mysql.Driver");
con = java.sql.DriverManager.getConnection(url, id, pass);

}catch(ClassNotFoundException cnfex){
cnfex.printStackTrace();

}
//String sql = "select o.data_ocorr, c.nome_cli, c.ende_cli, c.bai_cli from ocorrencia as o inner join cliente as c on o.cli_ocorr = c.codigo;";
String sql = "select count(a.cod_ocorr) as quant, b.descr_bairro from atend_ocorr as a inner join ocorrencia as o on a.cod_ocorr = o.codigo inner join cliente as c on o.cli_ocorr = c.codigo inner join motorista as m on a.cod_moto = m.cnh inner join viatura as v on a.cod_viat = v.renavan inner join bairro as b on c.bai_cli = b.codigo inner join cidade cd on c.cid_cli = cd.codigo group by b.descr_bairro;";

try{
s = con.createStatement();
rs = s.executeQuery(sql);
%>
<form name="rel">
<table border ="1" >
<thead>
    <tr>
        <th colspan="7">QUANTIDADE OCORRENCIAS POR BAIRRO</th>
    </tr>
<tr>
<th>Quantidade</th>
<th>Bairro</th>
</tr>
</thead> 
 <tbody>

<%
while( rs.next() ){
    //System.out.println("oi");
    //System.out.println(rs.getString("nome_cli"));
%>
<tr>
<td><%=rs.getString("quant")%></td>
<td><%=rs.getString("descr_bairro")%></td>

</tr>
<%
}
%>
<%
}
catch(Exception e){e.printStackTrace();}
finally{
if(rs!=null) rs.close();
if(s!=null) s.close();
if(con!=null) con.close();
}

%>
</tbody>
</table>
</form>

</body>
</html>
